# 1.引包
import pandas
import xlrd
import xlwt

data = []


class OperationExcel:

    def __init__( self, file_path ):
        self.workbook = pandas.read_excel(file_path)

    def get_data_info( self ):
        """获取表格详细信息"""

        for i in self.workbook.index.values:
            data_dict = self.workbook.loc[i].to_dict()
            data.append(data_dict)
        return data

    def read( self ,filepath):
        """
        读取表格数据
        :return:
        """
        work_book = xlrd.open_workbook(filepath)
        sheet = work_book.sheet_by_index(0)
        for i in range(0, sheet.nrows):
            row_value = sheet.row_values(i)
            user = {"username": row_value[0], "email": row_value[1], "password": row_value[2],
                    "repassword": row_value[3], "tel": row_value[4], "question": row_value[5], "answer": row_value[6]}
            data.append(user)

    def write( self ,filepath,question=0,answer = None):
        """
        写入表格
        :return:
        """

        work_book = xlwt.Workbook()
        sheet = work_book.add_sheet("用户注册信息")
        count = len(data)
        for i in range(0, count):
            row = data[i]
            sheet.write(i, 0, row["username"])
            sheet.write(i, 1, row["email"])
            sheet.write(i, 2, row["password"])
            sheet.write(i, 3, row["repassword"])
            sheet.write(i, 4, row["tel"])
            sheet.write(i, 5, row["question"])
            sheet.write(i, 6, row["answer"])
        work_book.save(filepath)


if __name__ == '__main__':
    filepath = '../data/test_register.xlsx'
    operation = OperationExcel(filepath)
